package com.jonlin.springboottest.utils.excel;

import com.jonlin.springboottest.annotation.OutputExcel;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * excel导出工具类(调用构造函数即可)
 */
public class OutputExcelUtil {
	private static Logger logger = LoggerFactory.getLogger(OutputExcelUtil.class);

	private XSSFWorkbook workbook = null;

	private XSSFSheet sheet = null;

	private List<ExcelEntity> excels = new ArrayList<ExcelEntity>();

    /**
     * excel导出构造函数
     *
     * @param fileName 文件名
     * @param list 数据集合
     * @param c 模板类
     * @param response 响应
     */
	public OutputExcelUtil(String fileName, List list, Class c, HttpServletResponse response) throws Exception {
//	    int i = 1/0;
        workbook = new XSSFWorkbook();  //创建Excel工作簿
        sheet = workbook.createSheet(); //创建一个工作表sheet
        sheet.setDefaultRowHeight((short) (15.625 * 26));  //设置行高
        sheet.setDefaultColumnWidth(20); // 设置默认列宽
        this.createFirstRow(c);   //创建表头
        this.setCells(list);    //拼写数据
        this.outputExcel(fileName, response); //导出
	}

    /**
     * 根据属性名获取对象属性值
     * @param fieldName 属性名
     * @param o 对象
     * @return
     */
	private static Object getFieldValueByName(String fieldName, Object o)  {
		try {
			String firstLetter = fieldName.substring(0, 1).toUpperCase();
			String getter = "get" + firstLetter + fieldName.substring(1);
			Method method = o.getClass().getMethod(getter, new Class[]{});
			Object value = method.invoke(o, new Object[]{});
			return value;
		} catch (Exception e) {
		    // 未取到，返回null
			return null;
		}
	}


    /**
     * 设置单元格样式
     * @return
     */
	private XSSFCellStyle getCallStyle() {
		XSSFCellStyle style = workbook.createCellStyle();   //初始化单元格样式
//        style.setBorderBottom(BorderStyle.THIN);    //下边框
//        style.setBorderRight(BorderStyle.THIN);     //右边框
		style.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
		style.setAlignment(HorizontalAlignment.CENTER); //水平居中
		return style;
	}

    /**
     * 根据实体类创建 标题行（表头）
     * @param c 实体类
     */
	private void createFirstRow(Class c) {
		//获取样式
		XSSFCellStyle style = getCallStyle();
		//创建标题
//		XSSFRow titleRow = sheet.createRow(0);
//		XSSFCell titleCell = titleRow.createCell(0);
//		titleCell.setCellStyle(style);
//		titleCell.setCellValue(title);

		//创建属性标题行
		XSSFRow row = sheet.createRow(0);

		//获取注解类的导出标题，并排序
		Field[] fields = c.getDeclaredFields();
		for (Field field : fields) {
			if (field.isAnnotationPresent(OutputExcel.class)) {
				OutputExcel outputExcel = field.getAnnotation(OutputExcel.class);
				excels.add(new ExcelEntity(outputExcel, field.getName()));
			}
		}
		Collections.sort(excels, (s1, s2) -> s1.getSort() - (s2.getSort()));  //排序
//		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, excels.size() - 1));    //主标题合并单元格
		//插入标题
		ExcelEntity excel = null;
		XSSFCell cell = null;
		for (int i = 0; i < excels.size(); i++) {
			excel = excels.get(i);  //获取excel数据
			cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(excel.getTitle());
		}
	}

	//拼写数据单元格
	private void setCells(List<Object> list) throws Exception {
		XSSFCellStyle style = this.getCallStyle();  //获取单元格样式
		XSSFRow row = null;
		XSSFCell cell = null;
		int rowIndex = 1;   //初始化行数下标
		for (Object obj : list) {   //遍历导出的对象集合
			if (obj == null) {
				break;
			}
			row = sheet.createRow(rowIndex);    //创建行
			for (int i = 0; i < excels.size(); i++) { //遍历单元格
				cell = row.createCell(i);   //创建单元格
				cell.setCellStyle(style);   //设置单元格样式
				Method method = obj.getClass().getMethod(excels.get(i).getFieldName()); //获取属性的get方法
				Object invoke = method.invoke(obj); //获取属性get方法的返回值
				cell.setCellValue(invoke == null ? "" : invoke.toString());   //单元格赋值
			}
			rowIndex++;
		}
	}

	//导出excel
	private void outputExcel(String fileName, HttpServletResponse response) throws IOException {
//		HttpServletResponse response = HttpUtil.getResponse();
//        response.reset();
		response.setContentType("application/octet-stream; charset=utf-8");
		response.setHeader("Content-Disposition", "attachment; filename=" + Encodes.urlEncode(fileName));
		workbook.write(response.getOutputStream());
	}

	/**
	 * 标题文字样式
	 * @param workbook 工作簿
	 * @return XSSFCellStyle
	 */
	public static XSSFCellStyle getTitleStyle(XSSFWorkbook workbook){
		XSSFCellStyle cellStyle0 = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setBold(true);
		font.setFontHeightInPoints((short)18);
		font.setColor(Font.COLOR_NORMAL);
		cellStyle0.setFont(font);
		cellStyle0.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle0.setAlignment(HorizontalAlignment.CENTER);
		return cellStyle0;
	}

	/**
	 * 副标题文字样式
	 * @param workbook 工作簿
	 * @return XSSFCellStyle
	 */
	public static XSSFCellStyle getSubTitleStyle(XSSFWorkbook workbook){
		XSSFCellStyle cellStyle0 = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setBold(true);
		font.setFontHeightInPoints((short)12);
		font.setColor(Font.COLOR_NORMAL);
		cellStyle0.setFont(font);
		cellStyle0.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle0.setAlignment(HorizontalAlignment.CENTER);
		return cellStyle0;
	}

	/**
	 * 靠左
	 * @param workbook 工作簿
	 * @return XSSFCellStyle
	 */
	public static XSSFCellStyle getSubTitleLeftStyle(XSSFWorkbook workbook){
		XSSFCellStyle cellStyle0 = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setBold(true);
		font.setFontHeightInPoints((short)12);
		font.setColor(Font.COLOR_NORMAL);
		cellStyle0.setFont(font);
		cellStyle0.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle0.setAlignment(HorizontalAlignment.LEFT);
		return cellStyle0;
	}


	/**
	 * 靠右
	 * @param workbook 工作簿
	 * @return XSSFCellStyle
	 */
	public static XSSFCellStyle getSubTitleRightStyle(XSSFWorkbook workbook){
		XSSFCellStyle cellStyle0 = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setBold(true);
		font.setFontHeightInPoints((short)12);
		font.setColor(Font.COLOR_NORMAL);
		cellStyle0.setFont(font);
		cellStyle0.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle0.setAlignment(HorizontalAlignment.RIGHT);
		return cellStyle0;
	}

	/**
	 * 表头样式
	 * @param workbook 工作簿
	 * @return XSSFCellStyle
	 */
	public static XSSFCellStyle getTableHeaderStyle(XSSFWorkbook workbook){
		XSSFCellStyle cellStyle0 = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setBold(true);
		font.setFontHeightInPoints((short)10);
		font.setColor(Font.COLOR_NORMAL);
		cellStyle0.setFont(font);
		cellStyle0.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle0.setAlignment(HorizontalAlignment.CENTER);
		return cellStyle0;
	}

	/**
	 * 表内容 基本样式
	 * @param workbook 工作簿
	 * @return XSSFCellStyle
	 */
	public static XSSFCellStyle getTableContentStyle(XSSFWorkbook workbook){
		XSSFCellStyle cellStyle0 = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setBold(false);
		font.setFontHeightInPoints((short)10);
		font.setColor(Font.COLOR_NORMAL);
		cellStyle0.setFont(font);
		return cellStyle0;
	}

	/**
	 * 表内容居中
	 * @param workbook 工作簿
	 * @return XSSFCellStyle
	 */
	public static XSSFCellStyle getTableContentCenterStyle(XSSFWorkbook workbook){
		XSSFCellStyle cellStyle0 = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setBold(false);
		font.setFontHeightInPoints((short)10);
		font.setColor(Font.COLOR_NORMAL);
		cellStyle0.setFont(font);
		cellStyle0.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle0.setAlignment(HorizontalAlignment.CENTER);
		return cellStyle0;
	}

	/**
	 * 表内容靠左
	 * @param workbook 工作簿
	 * @return XSSFCellStyle
	 */
	public static XSSFCellStyle getTableContentLeftStyle(XSSFWorkbook workbook){
		XSSFCellStyle cellStyle0 = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setBold(false);
		font.setFontHeightInPoints((short)10);
		font.setColor(Font.COLOR_NORMAL);
		cellStyle0.setFont(font);
		cellStyle0.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle0.setAlignment(HorizontalAlignment.CENTER);
		return cellStyle0;
	}

	/**
	 * 表内容靠右
	 * @param workbook 工作簿
	 * @return XSSFCellStyle
	 */
	public static XSSFCellStyle getTableContentRightStyle(XSSFWorkbook workbook){
		XSSFCellStyle cellStyle0 = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setBold(false);
		font.setFontHeightInPoints((short)10);
		font.setColor(Font.COLOR_NORMAL);
		cellStyle0.setFont(font);
		cellStyle0.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle0.setAlignment(HorizontalAlignment.RIGHT);
		return cellStyle0;
	}

}
